﻿using System;
using System.Data;
using System.Web;
using BP.DA;
using BP.Web;
using BP.GPM;


namespace CCOA.App.AddressList.CTT
{
    /// <summary>
    /// Handler 的摘要说明
    /// </summary>
    public class Handler : WebPage
    {
        
        public string getUTF8ToString(string param)
        {
            return HttpUtility.UrlDecode(Request[param], System.Text.Encoding.UTF8);
        }

        //页面加载
        protected void Page_Load(object sender, EventArgs e)
        {
            if (BP.Web.WebUser.No == null)
                return;

            string method = string.Empty;
            //返回值
            string s_responsetext = string.Empty;
            if (!string.IsNullOrEmpty(Request["method"]))
                method = Request["method"].ToString();

            switch (method)
            {
                case "Index_InitDept"://获取getDept树  
                    s_responsetext = Index_InitDept();
                    break;
                case "Index_LoadEmp"://加载部门人员
                    s_responsetext = Index_LoadEmp();
                    break;
                case "Index_Search"://查询人员
                    s_responsetext = Index_Search();
                    break;
            }

            if (string.IsNullOrEmpty(s_responsetext))
                s_responsetext = "";
            //组装ajax字符串格式,返回调用客户端
            Response.Charset = "UTF-8";
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            Response.ContentType = "text/html";
            Response.Expires = 0;
            Response.Write(s_responsetext);
            Response.End();
        }
        
        /// <summary>
        /// 加载部门树
        /// </summary>
        /// <returns></returns>
        private string Index_InitDept()
        {
            string rootNo = getUTF8ToString("rootNo");
   
            Depts dt = new Depts();
            int row = dt.RetrieveAll();

            return BP.Tools.Entitis2Json.ConvertEntitis2GenerTree(dt, rootNo);
        }


        /// <summary>
        /// 获取该部门的所有人员
        /// </summary>
        /// <returns></returns>
        private string Index_LoadEmp()
        {
            string deptNo = getUTF8ToString("deptNo");
            if (string.IsNullOrEmpty(deptNo))
            {
                return "{ total: 0, rows: [] }";
            }
            string orderBy = getUTF8ToString("orderBy");
            //默认  部门职务查询
            if (string.IsNullOrEmpty(orderBy))
            {
                orderBy = " FK_DUTY ASC ";
            }

            string searchText = getUTF8ToString("searchText");
            string addQue = "";
            if (!string.IsNullOrEmpty(searchText))
            {
                addQue = "  AND (emp_.No like '%" + searchText + "%' or emp_.Name like '%" + searchText + "%') ";
            }

            string pageNumber = getUTF8ToString("pageNumber");
            int iPageNumber = string.IsNullOrEmpty(pageNumber) ? 1 : Convert.ToInt32(pageNumber);
            //每页多少行
            string pageSize = getUTF8ToString("pageSize");
            int iPageSize = string.IsNullOrEmpty(pageSize) ? 9999 : Convert.ToInt32(pageSize);

            //sta_.Name as stationName,
            string sql = "(select emp_.*,dept_.Name as deptName,duty_.Name as dutyName from port_emp emp_ "
            + " left join Port_Dept dept_ on emp_.FK_Dept = dept_.No "
            + " left join Port_Duty duty_ on emp_.FK_Duty = duty_.No "
            //+ " left join Port_DeptEmpStation empSta_ on empSta_.FK_Dept = emp_.FK_Dept and empSta_.Fk_Emp = emp_.No"
            //+ " left join Port_Station sta_ on sta_.No = empSta_.Fk_Station "
            + " where emp_.no in (select fk_emp from Port_DeptEmp where fk_dept='" + deptNo + "') "
                + addQue + " ) dbSo ";

            return DBPaging(sql, iPageNumber, iPageSize, "dbSo.No", orderBy);

        }

        /// <summary>
        /// 根据用户账号、工号、姓名或手机号
        /// </summary>
        /// <returns></returns>
        private string Index_Search()
        {
            string pageNumber = getUTF8ToString("pageNumber");
            int iPageNumber = string.IsNullOrEmpty(pageNumber) ? 1 : Convert.ToInt32(pageNumber);
            //每页多少行
            string pageSize = getUTF8ToString("pageSize");
            int iPageSize = string.IsNullOrEmpty(pageSize) ? 9999 : Convert.ToInt32(pageSize);

            string searchText = getUTF8ToString("searchText");
            string orderBy = getUTF8ToString("orderBy");
            string addQue = "";

            //默认  部门职务查询
            if (string.IsNullOrEmpty(orderBy))
            {
                orderBy = " FK_DUTY ASC ";
            }

            if (!string.IsNullOrEmpty(searchText))
            {
                addQue = "  AND (emp_.No like '%" + searchText + "%' or emp_.Name like '%" + searchText + "%') ";
            }

            //sta_.Name as stationName,
            string sql = "(select emp_.*,dept_.Name as deptName,duty_.Name as dutyName from port_emp emp_ "
            + " left join Port_Dept dept_ on emp_.FK_Dept = dept_.No "
            + " left join Port_Duty duty_ on emp_.FK_Duty = duty_.No "
            //+ " left join Port_DeptEmpStation empSta_ on empSta_.FK_Dept = emp_.FK_Dept and empSta_.Fk_Emp = emp_.No"
            //+ " left join Port_Station sta_ on sta_.No = empSta_.Fk_Station "
            + " where 1=1 "
            + addQue + " ) dbSo ";

            return DBPaging(sql, iPageNumber, iPageSize, "dbSo.No", orderBy);
        
        }

        /// <summary>
        /// 以下算法只包含 oracle mysql sqlserver 三种类型的数据库 qin
        /// </summary>
        /// <param name="dataSource">表名</param>
        /// <param name="pageNumber">当前页</param>
        /// <param name="pageSize">当前页数据条数</param>
        /// <param name="key">计算总行数需要</param>
        /// <param name="orderKey">排序字段</param>
        /// <returns></returns>
        private string DBPaging(string dataSource, int pageNumber, int pageSize, string key, string orderKey)
        {
            string sql = "";
            string orderByStr = "";

            if (!string.IsNullOrEmpty(orderKey))
                orderByStr = " ORDER BY " + orderKey;

            switch (DBAccess.AppCenterDBType)
            {
                case DBType.Oracle:
                    int beginIndex = (pageNumber - 1) * pageSize + 1;
                    int endIndex = pageNumber * pageSize;

                    sql = "SELECT * FROM ( SELECT A.*, ROWNUM RN " +
                        "FROM (SELECT * FROM  " + dataSource + orderByStr + ") A WHERE ROWNUM <= " + endIndex + " ) WHERE RN >=" + beginIndex;
                    break;
                case DBType.MSSQL:
                    sql = "SELECT TOP " + pageSize + " * FROM " + dataSource + " WHERE " + key + " NOT IN  ("
                    + "SELECT TOP (" + pageSize + "*(" + pageNumber + "-1)) " + key + " FROM " + dataSource + " )" + orderByStr;
                    break;
                case DBType.MySQL:
                    pageNumber -= 1;
                    sql = "select * from  " + dataSource + orderByStr + " limit " + pageNumber + "," + pageSize;
                    break;
                default:
                    throw new Exception("暂不支持您的数据库类型.");
            }

            DataTable DTable = DBAccess.RunSQLReturnTable(sql);

            int totalCount = DBAccess.RunSQLReturnCOUNT("select " + key + " from " + dataSource);

            return DataTableConvertJson.DataTable2Json(DTable, totalCount);
        }
       
    }
}